package per.forest.database.meta;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBMetaTool {
	
	public static Map<String, Table> getTableByDataBaseMap(Connection connection,
			String schemaPattern, DataBaseType dbType) throws Exception{
		Map<String, Table> result = new HashMap<String, Table>();
		
		List<Table> tableByDataBase = getTableByDataBase(connection, schemaPattern, dbType);
		for (Table table : tableByDataBase) {
			result.put(table.getName(), table);
		}
		
		return result;
	}

	public static List<Table> getTableByDataBase(Connection connection,
			String schemaPattern, DataBaseType dbType) throws Exception {
		List<Table> result = new ArrayList<Table>();

		DatabaseMetaData databaseMetaData = null;
		try {
			databaseMetaData = connection.getMetaData();
		} catch (SQLException e) {
			String msg = MessageFormat.format("获取库{0}的表信息过程中，获取库的元数据时发生错误。",
					schemaPattern);
			throw new Exception(msg, e);
		}

		ResultSet tables = null;
		try {
			tables = databaseMetaData.getTables(connection.getCatalog(),
					schemaPattern, null, new String[] { "TABLE" });

			if (tables != null) {
				while (tables.next()) {
					String tblName = tables.getString(3);

					if (tblName.indexOf("==$") >= 0) {
						continue;
					}

					Table table = new Table();
					table.setName(tblName);

					List<Column> columns = getColumnsByTable(schemaPattern,
							tblName, connection, dbType);
					table.setColumns(columns);
					result.add(table);
				}
			}
		} catch (SQLException e) {
			String msg = MessageFormat.format("获取库{0}的表信息过程中，获取表格的元数据时发生错误。",
					schemaPattern);
			throw new Exception(msg, e);
		} finally {
			try {
				if (tables != null) {
					tables.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return result;
	}

	public static List<Column> getColumnsByTable(String schemaPattern,
			String tableName, Connection connection, DataBaseType dbType)
			throws Exception {
		List<Column> result = new ArrayList<Column>();

		ResultSetMetaData rm = null;
		Statement st = null;
		ResultSet rs = null;
		try {

			DatabaseMetaData databaseMetaData = null;
			try {
				databaseMetaData = connection.getMetaData();
			} catch (SQLException e) {
				String msg = MessageFormat.format(
						"获取表{0}的字段过程中，获取表格的元数据时发生错误。", tableName);
				throw new Exception(msg, e);
			}

			List<String> primaryKeys = getPkColumnNames(connection,
					databaseMetaData, schemaPattern, tableName);
			List<String> foreignKeys = getFkColumnNames(connection,
					databaseMetaData, schemaPattern, tableName);
			Map<String, String> columnRemarks = getColumnRemarks(dbType, connection, schemaPattern, tableName);
			try {
				st = connection.createStatement();
				String sqlClause = getSqlClause(dbType, tableName);
				rs = st.executeQuery(sqlClause);
				rm = rs.getMetaData();
			} catch (SQLException e) {
				String msg = MessageFormat.format(
						"获取表{0}的字段过程中，通过查询语句获取表格的元数据时发生错误。", tableName);
				throw new Exception(msg, e);
			}

			for (int i = 1; i <= rm.getColumnCount(); i++) {
				String colName = rm.getColumnName(i);
				int precision = rm.getPrecision(i);
				int scale = rm.getScale(i);

				int type = rm.getColumnType(i);
				String javaType = SqlJavaTypeTool.sqlTypeToJavaType(type)
						.getName();

				boolean pk = primaryKeys.contains(colName);
				boolean fk = foreignKeys.contains(colName);
				String remark = columnRemarks.get(colName);

				Column column = new Column();

				column.setTableName(tableName);
				column.setName(colName);
				column.setType(type);
				column.setJavaType(javaType);
				column.setScale(scale);
				column.setPrecision(precision);
				column.setPk(pk);
				column.setFk(fk);
				if(remark != null){
					column.setRemark(remark);
				}

				result.add(column);
			}

		} catch (SQLException e) {
			String msg = MessageFormat.format("获取字段过程中，处理表{0}的字段时发生错误。",
					tableName);
			throw new Exception(msg, e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}

				if (st != null) {
					st.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		try {
			if (rs != null) {
				rs.close();
			}

			if (st != null) {
				st.close();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}

	private static Map<String, String> getColumnRemarks(DataBaseType dbType,
			Connection connection, String schemaPattern, String tableName)
			throws Exception {
		Map<String, String> result = new HashMap<String, String>();

		if (dbType == DataBaseType.MYSQL) {
			Statement st = null;
			ResultSet rs = null;
			try {
				String sql = MessageFormat
						.format("select COLUMN_NAME,column_comment from INFORMATION_SCHEMA.Columns where table_name=''{0}'' and table_schema=''{1}''",
								tableName, schemaPattern);
				st = connection.createStatement();
				rs = st.executeQuery(sql);
				while (rs.next()) {
					result.put(rs.getString("COLUMN_NAME"),
							rs.getString("column_comment"));
				}
			} catch (SQLException e) {
				String msg = MessageFormat.format("获取字段过程中，处理表{0}的字段时发生错误。",
						tableName);
				throw new Exception(msg, e);
			} finally {
				if (st != null) {
					try {
						st.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}

				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}

		return result;
	}

	private static String getSqlClause(DataBaseType dbType, String tableName) {
		String result = null;

		if (dbType == DataBaseType.MSSQL)
			result = "select * from [" + tableName + "] where 1=2";
		else {
			result = "select * from " + tableName + " where 1=2";
		}

		return result;
	}

	private static List<String> getPkColumnNames(Connection connection,
			DatabaseMetaData metaData, String schemaPattern, String tableName)
			throws Exception {
		List<String> result = new ArrayList<String>();

		ResultSet keyRs4P = null;
		try {
			keyRs4P = metaData.getPrimaryKeys(connection.getCatalog(),
					schemaPattern, tableName);
		} catch (SQLException e) {
			String msg = MessageFormat.format("获取表{0}主键过程中，获取元数据时发生错误。",
					tableName);
			throw new Exception(msg, e);
		}
		try {
			while (keyRs4P.next()) {
				result.add(keyRs4P.getString(4));
			}
		} catch (SQLException e) {
			String msg = MessageFormat.format("获取表{0}主键过程中，处理元数据时发生错误。",
					tableName);
			throw new Exception(msg, e);
		}

		return result;
	}

	private static List<String> getFkColumnNames(Connection connection,
			DatabaseMetaData metaData, String schemaPattern, String tableName)
			throws Exception {
		List<String> result = new ArrayList<String>();
		ResultSet keyRs4F = null;
		try {
			keyRs4F = metaData.getImportedKeys(connection.getCatalog(),
					schemaPattern, tableName);
		} catch (SQLException e) {

			String msg = MessageFormat.format("获取表{0}外键过程中，获取元数据时发生错误。",
					tableName);
			throw new Exception(msg, e);
		}
		try {
			while (keyRs4F.next()) {
				result.add(keyRs4F.getString(8));
			}
		} catch (SQLException e) {
			String msg = MessageFormat.format("获取表{0}外键过程中，处理元数据时发生错误。",
					tableName);
			throw new Exception(msg, e);
		}

		return result;
	}
}
